﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using portfolio.Common;

namespace portfolio.DataAccess.DancersDB
{
    public class DancersDBDAO
    {
        public List<DancersDBData> GetAllSongs()
        {
            List<DancersDBData> dancersDBData = new List<DancersDBData>();

            using (var db = new DancersDBContext())
            {
                var songQuery = from songs in db.Songs
                                orderby songs.Song1 ascending
                                select new
                                       {
                                           songs.SongID,
                                           songs.Song1,
                                           songs.Artist,
                                           songs.BPM,
                                           songs.SongLengthInSeconds,
                                           dances = from d in songs.DanceStyles
                                                    orderby d.DanceName
                                                    select new { d.DanceIdentifier, d.DanceName }
                                       };

                // Save songs and their associated dances to the DancersDBData List
                foreach (var item in songQuery)
                {
                    DancersDBData dancersData = new DancersDBData();

                    dancersData = PopulateSongData(item.SongID, item.Song1, item.Artist, item.BPM, item.SongLengthInSeconds);

                    // Get Dance Style data in UI and Data Manipulation forms
                    foreach (var dance in item.dances)
                    {
                        dancersData = PopulateDanceStyleList(dancersData, dance.DanceIdentifier, dance.DanceName);
                    }

                    dancersDBData.Add(dancersData);
                }
            }
            return dancersDBData;
        }

        public List<DancersDBData> GetAllSongsForDances(List<string> danceIdentifiers)
        {
            List<DancersDBData> dancersDBData = new List<DancersDBData>();
            DancersDBData dancersData = new DancersDBData();

            using (var db = new DancersDBContext())
            {
                foreach (string danceStyle in danceIdentifiers)
                {
                    var query = from songs in db.Songs
                                from dances in songs.DanceStyles
                                where dances.DanceIdentifier == danceStyle
                                orderby songs.Song1 ascending
                                select new
                                {
                                    songs.SongID,
                                    songs.Song1,
                                    songs.Artist,
                                    songs.BPM,
                                    songs.SongLengthInSeconds,
                                    dances.DanceIdentifier,
                                    dances.DanceName
                                };

                    foreach (var item in query)
                    {
                        dancersData = PopulateSongData(item.SongID, item.Song1, item.Artist, item.BPM, item.SongLengthInSeconds);
                        dancersData = PopulateDanceStyleList(dancersData, item.DanceIdentifier, item.DanceName);
                        dancersDBData.Add(dancersData);
                    }
                }

            }

            return dancersDBData;
        }

        public DancersDBData GetSongForSongID(int songID)
        {
            DancersDBData dancersData = new DancersDBData();

            using(var db = new DancersDBContext())
            {
                var songQuery = (from songs in db.Songs
                                 from dances in songs.DanceStyles
                                 where songs.SongID == songID
                                 select songs).FirstOrDefault();

                dancersData = PopulateSongData(songQuery.SongID, songQuery.Song1, songQuery.Artist, songQuery.BPM, songQuery.SongLengthInSeconds);
                foreach (var dance in songQuery.DanceStyles)
                {
                    dancersData = PopulateDanceStyleList(dancersData, dance.DanceIdentifier, dance.DanceName);
                }
            }

            return dancersData;
        }

        // Return a list of Dance Styles containing the Dance Names and Dance Identifiers 
        public List<DanceStyleData> GetAllDanceStyles()
        {
            List<DanceStyleData> danceStyleData = new List<DanceStyleData>();

            using (var db = new DancersDBContext())
            {
                var danceStyles = from dances in db.DanceStyles
                                 orderby dances.DanceName
                                 select new { dances.DanceIdentifier, dances.DanceName };

                foreach (var dance in danceStyles)
                {
                    DanceStyleData danceStyle = new DanceStyleData();

                    danceStyle.DanceIdentifier = dance.DanceIdentifier;
                    danceStyle.DanceName = dance.DanceName;

                    danceStyleData.Add(danceStyle);
                }
            }

            return danceStyleData;
        }

        public List<DanceStyleData> GetAllDanceStylesForSong(int songID)
        {
            List<DanceStyleData> danceStyleData = new List<DanceStyleData>();

            using (var db = new DancersDBContext())
            {
                var danceStyles = from dances in db.DanceStyles
                                  from songs in dances.Songs
                                  where songs.SongID == songID
                                  orderby dances.DanceName
                                  select new { dances.DanceIdentifier, dances.DanceName };

                foreach (var dance in danceStyles)
                {
                    DanceStyleData danceStyle = new DanceStyleData();

                    danceStyle.DanceIdentifier = dance.DanceIdentifier;
                    danceStyle.DanceName = dance.DanceName;

                    danceStyleData.Add(danceStyle);
                }
            }

            return danceStyleData;
        }

        public void InsertSong(DancersDBData dancersDBData)
        {
            using(var db = new DancersDBContext())
            {
                var song = new Song
                {
                    Song1 = dancersDBData.Song,
                    Artist = dancersDBData.Artist,
                    BPM = dancersDBData.BPM,
                    SongLengthInSeconds = dancersDBData.SongLengthInSeconds
                };

                foreach (var dance in dancersDBData.DanceStylesList)
                {
                    var danceStyle = db.DanceStyles.FirstOrDefault(d => d.DanceIdentifier == dance.DanceIdentifier);
                    song.DanceStyles.Add(danceStyle);
                }
                
                db.Songs.Add(song);
                db.SaveChanges();
            }
        }

        public void UpdateSong(DancersDBData dancersDBData)
        {
            using (var db = new DancersDBContext())
            {
                var song = (from dances in db.DanceStyles
                            from songs in dances.Songs
                            where songs.SongID == dancersDBData.SongID
                            select songs).FirstOrDefault();

                if (song != null)
                {
                    song.Song1 = dancersDBData.Song;
                    song.Artist = dancersDBData.Artist;
                    song.BPM = dancersDBData.BPM;
                    song.SongLengthInSeconds = dancersDBData.SongLengthInSeconds;

                    var styles = from dances in db.DanceStyles
                                 from songs in dances.Songs
                                 where songs.SongID == dancersDBData.SongID
                                 select dances;

                    foreach (var style in styles)
                    {
                        song.DanceStyles.Remove(style);
                    }

                    foreach (DanceStyleData danceStyle in dancersDBData.DanceStylesList)
                    {
                        var style = (from dances in db.DanceStyles
                                     where dances.DanceIdentifier == danceStyle.DanceIdentifier
                                     select dances).FirstOrDefault();
                        

                        song.DanceStyles.Add(style);
                    }

                    db.SaveChanges();
                } 
            }
        }

        public void DeleteSong(int songID)
        {
            using (var db = new DancersDBContext())
            {
                var song = (from dances in db.DanceStyles
                           from songs in dances.Songs
                           where songs.SongID == songID
                           select songs).FirstOrDefault();

                if (song != null)
                {
                    db.Songs.Remove(song);
                    db.SaveChanges();
                }
            }
        }

        public string GetDancersDBScreenForValue(string value)
        {
            string dancersDBControl = "";
            using (var db = new DancersDBContext())
            {
                dancersDBControl = (from dancersScreen in db.DancersDBScreens
                                    where dancersScreen.DancersDBValue == value
                                    select dancersScreen.DancersDBControl).FirstOrDefault();
            }
            return dancersDBControl;
        }

        public bool Login(string username, string password)
        {
            bool validUser = false;
            using(var db = new DancersDBContext())
            {
                var users = from login in db.DancersDBLoginInfoes
                               where login.username == username && login.password == password
                               select login.username;

                if (users.Count() == 1)
                {
                    validUser = true;
                }
            }

            return validUser;
        }

        #region Helper Methods
        private DancersDBData PopulateSongData(int songID, string song, string artist, int? bpm, int? songLengthInSeconds)
        {
            DancersDBData dancersData = new DancersDBData();

            dancersData.SongID = songID;
            dancersData.Song = song;
            dancersData.Artist = artist;
            dancersData.BPM = bpm;
            dancersData.SongLengthInSeconds = songLengthInSeconds;
            dancersData.SongLength = dancersData.ConvertSongLengthToMinutesAndSeconds(songLengthInSeconds.Value);

            return dancersData;
        }

        private DancersDBData PopulateDanceStyleList(DancersDBData dancersData, string danceIdentifier, string danceName)
        {
            DanceStyleData danceStyle = new DanceStyleData();

            // Create the Dance Styles List for data manipulation
            danceStyle.DanceIdentifier = danceIdentifier;
            danceStyle.DanceName = danceName;
            dancersData.DanceStylesList.Add(danceStyle);

            // Create the UI displayable list
            dancersData.DanceIdentifier += danceIdentifier + ",";
            dancersData.DanceName += danceName + "<br />";

            return dancersData;
        }
        #endregion
    }
}
